﻿

--入职审批
CREATE proc [dbo].[proc_Employee_Entry_Audit]
(
	@Id int,
	@Audit bit,
	@Memo text,
	@UserName nvarchar (50),
	@AuditTime nvarchar(50)

)
as
		begin
			--审批
			update employee_entry set Audit=@Audit,Memo=@Memo,AuditPerson=@UserName,AuditTime=@AuditTime where id=@id
			--审批通过的场合,入职设置生效
			if(@audit=1)
				begin
					--1.生成员工编号
					declare @eid int,@ruzhidate datetime,@ruzhiyear nvarchar(4),@ZzDate datetime
					set @eid = (select eid from Employee_Entry where id=@id)
					set @ruzhidate = (select TRuzhiDate from Employee_Entry where id=@id)
					set @ruzhiyear = year(@ruzhidate)
					declare @Enumber varchar(10)
					declare @tempNumber varchar(10)
					--if((select count(id) from employee)=0)
					--set @Enumber = @ruzhiyear + '0001'
					--else
					--begin
					--	set @tempNumber = (select top 1 Enumber from employee where enumber <> '9'   order by convert(integer,substring(Enumber,5,15)) desc)
					--	set @Enumber = convert(integer,substring(@tempNumber,5,15)) + 1
					--	if (len(@Enumber)=1)
					--		set @Enumber = @ruzhiyear +'000' + @Enumber
					--	else if (len(@Enumber)=2)
					--		set @Enumber = @ruzhiyear +'00' + @Enumber
					--	else if (len(@Enumber)=3)
					--		set @Enumber = @ruzhiyear +'0' + @Enumber
					--	else
					--		set @Enumber = @ruzhiyear + @Enumber
					--end
					
	Declare @rq varchar(10),@shl int,@bh char(12)
Set @rq=Substring(Convert(varchar(10),GETDATE(),112),1,4)

Select @shl=max(enumber) From employee Where Left(enumber,4)=@rq AND enumber <> '9'

If @shl IS null
	Set @Enumber=@rq+'0001'
Else
	Begin
		Set @shl=@shl+1
		Set @Enumber=convert(varchar,@shl)
	END
		
					
					
					--2.更新员工表 
					update employee 
						set
						    UserName =@Enumber,
						    Enumber = @Enumber,
						    departmentId=(select TDepartmentId from Employee_Entry where id=@id),
					                  postid=(select TPostid from Employee_Entry where id=@id),
						    gradeid=(select TGradeid from Employee_Entry where id=@id),
						    pluralist=(select TPluralist from Employee_Entry where id=@id),
						    rtx=(select TRtx from Employee_Entry where id=@id),
						    wageNumber=(select TWageNumber from Employee_Entry where id=@id),
						    wageCardNo=(select TWageCardNo from Employee_Entry where id=@id),
						    RuzhiDate=(select TRuzhiDate from Employee_Entry where id=@id),
						    postcategory=(select TPostCategory from Employee_Entry where id=@id),
						    ProbationFrom =( select TProbationFrom  from Employee_Entry where id=@id),
						    ProbationTo =( select TProbationTo  from Employee_Entry where id=@id),
						    ZhiWu =(SELECT Employee_Entry.TZhiWu FROM Employee_Entry WHERE id=@Id),
						    XZJB = (SELECT Employee_Entry.TXZJB FROM Employee_Entry WHERE id=@Id),
							IsCore = (SELECT Employee_Entry.IsCore FROM Employee_Entry WHERE id=@Id),
						    State = 3 --员工状态变为试用
						where id = @eid

					--6.插入帐号表
					declare @rtx varchar(50),@email varchar(50),@companyid int,@ename varchar(50),@departmentid int
					set @rtx = (select rtx from Employee where id=@eid)
					set @email = (select email from Employee where id=@eid)
					set @companyid = (select companyid from Employee where id=@eid)
					set @ename = (select ename from Employee where id=@eid)
					SET @departmentid = (SELECT DepartmentId from Employee where id=@eid)
					--insert into aspnet_Users(username,[password],rtx,email,companyid,[name],DepartmentId) values(@Enumber,'7C4A8D09CA3762AF61E59520943DC26494F8941B',@rtx,@email,@companyid,@ename,@departmentid)
					insert into aspnet_Users(username,[password],rtx,email,companyid,[name],DepartmentId) values(@Enumber,'8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92',@rtx,@email,@companyid,@ename,@departmentid)

					--3.插入转正申请表
					insert into Employee_ProbationPassed(Eid) values(@eid)
					--4.插入岗位异动表
					--insert into employee_postmove(Eid,EName,BCompanyid,ACompanyid) values(@eid,@ename,@companyid,@companyid)
					--5.插入单位异动表
					--insert into employee_CompanyMove(Eid,EName,BCompanyid,ACompanyid) values(@eid,@ename,0,@companyid) 
				
				Select @eid as 'RETUNR_V'
				end
		end
RETURN
